﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using MODEL;
using Newtonsoft.Json;

namespace DAL
{
    public class udal
    {
        SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=fjc;Integrated Security=True");
        //懒汉模式
        private udal()
        {

        }
        private static udal dal;
        private static readonly object obj = new object();
        public static udal getdal()
        {
            lock (obj)
            {
                if (dal == null)
                {
                    dal = new udal();
                }
                return dal;
            }
        }
        /// <summary>
        /// 用户注册
        /// </summary>
        /// <param name="u"></param>
        /// <returns></returns>
        public int regist(uinfor u)
        {
            con.Open();
            string str = "insert into userinfor values('" + u.uname + "','" + u.upwd + "','" + u.ucard + "',4)";
            SqlCommand com = new SqlCommand(str, con);
            int i = com.ExecuteNonQuery();
            con.Close();
            return i;
        }
        /// <summary>
        /// 判断用户名是否已存在
        /// </summary>
        /// <param name="uname"></param>
        /// <returns></returns>
        public int isregist(string uname)
        {
            con.Open();
            string str = "select count(*) from  userinfor where uname='" + uname + "'";
            SqlCommand com = new SqlCommand(str, con);
            int i = (int)com.ExecuteScalar();
            con.Close();
            return i;
        }
        /// <summary>
        /// 登录
        /// </summary>
        /// <param name="uname"></param>
        /// <param name="upwd"></param>
        /// <returns></returns>
        public int login(string uname, string upwd)
        {
            con.Open();
            string str = "select count(*) from  userinfor where uname='" + uname + "' and upwd='" + upwd + "'";
            SqlCommand com = new SqlCommand(str, con);
            int i = (int)com.ExecuteScalar();
            con.Close();
            return i;
        }
        /// <summary>
        /// 判断登录人的角色
        /// </summary>
        /// <param name="uname"></param>
        /// <returns></returns>
        public string selrole(string uname)
        {
            con.Open();
            string str = "select rname from roleinfor,userinfor where roleinfor.rid=userinfor.rid  and uname='" + uname + "'";
            SqlCommand com = new SqlCommand(str, con);
            string a = com.ExecuteScalar().ToString();
            con.Close();
            return a;
        }
        /// <summary>
        /// 显示登录人的权限
        /// </summary>
        /// <param name="rname"></param>
        /// <returns></returns>
        public List<uinfor> showquanxian(string rname)
        {
            string str = "select * from limit join relation on limit.qid=relation.qid join roleinfor  on roleinfor.rid=relation.rid  where rname='" + rname + "'";
            SqlDataAdapter da = new SqlDataAdapter(str, con);
            DataTable dt = new DataTable();
            da.Fill(dt);
            var list2 = JsonConvert.SerializeObject(dt);
            var list = JsonConvert.DeserializeObject<List<uinfor>>(list2);
            return list;
        }
        /// <summary>
        /// 显示医院信息
        /// </summary>
        /// <returns></returns>
        public List<uinfor> showDoctor()
        {
            //sql语句
            string str = "select * from hospital";
            //适配器对象
            SqlDataAdapter da = new SqlDataAdapter(str, con);
            //数据表集
            DataTable dt = new DataTable();
            //数据填充
            da.Fill(dt);
            //序列化
            var list = JsonConvert.SerializeObject(dt);
            //反序列化
            var result = JsonConvert.DeserializeObject<List<uinfor>>(list);
            return result;
        }
        /// <summary>
        /// 添加挂号记录表
        /// </summary>
        /// <param name="u"></param>
        /// <returns></returns>
        public int ADDregister(uinfor u)
        {
            //异常处理机制
            try
            {
                //打开数据库
                con.Open();
                //SQL语句
                string str = "insert into record values('" + u.uname + "',getdate(),'" + u.zname + "','" + u.ucard + "','" + u.number + "')";
                //命令对象
                SqlCommand comd = new SqlCommand(str, con);
                //获取行数
                int i = comd.ExecuteNonQuery();
                //关闭数据库
                con.Close();
                return i;
            }
            catch (Exception)
            {

                return 0;
            }
            finally
            {
                GC.Collect();
            }
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DELETEregister(int id)
        {
            //异常处理机制
            try
            {
                //打开数据库
                con.Open();
                //SQL语句
                string str = "delete from record where jid=" + id + "";
                //命令对象
                SqlCommand comd = new SqlCommand(str, con);
                //获取行数
                int i = comd.ExecuteNonQuery();
                //关闭数据库
                con.Close();
                return i;
            }
            catch (Exception)
            {

                return 0;
            }
            finally
            {
                GC.Collect();
            }
        }
        /// <summary>
        /// 显示挂号记录表
        /// </summary>
        /// <returns></returns>
        public List<uinfor> show()
        {
            //sql语句
            string str = "select * from record";
            //适配器对象
            SqlDataAdapter da = new SqlDataAdapter(str, con);
            //数据表集
            DataTable dt = new DataTable();
            //数据填充
            da.Fill(dt);
            //序列化
            var list = JsonConvert.SerializeObject(dt);
            //反序列化
            var result = JsonConvert.DeserializeObject<List<uinfor>>(list);
            return result;
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="u"></param>
        /// <returns></returns>
        public int UPDregister(uinfor u)
        {
            //异常处理机制
            try
            {
                //打开数据库
                con.Open();
                //SQL语句
                string str = "update  record set uname='" + u.uname + "',zname='" + u.zname + "',ucard='" + u.ucard + "',number='" + u.number + "' where jid='"+u.jid+"'";
                //命令对象
                SqlCommand comd = new SqlCommand(str, con);
                //获取行数
                int i = comd.ExecuteNonQuery();
                //关闭数据库
                con.Close();
                return i;
            }
            catch (Exception)
            {

                return 0;
            }
            finally
            {
                GC.Collect();
            }
        }
        /// <summary>
        /// 用户与专家交流显示
        /// </summary>
        /// <returns></returns>
        public List<uinfor> Showurealation()
        {
            con.Open();
            SqlDataAdapter adp = new SqlDataAdapter("select uname,zname,uquestion,zanswer from urealation", con);
            DataTable dt = new DataTable();
            adp.Fill(dt);
            var json = JsonConvert.SerializeObject(dt);
            var list = JsonConvert.DeserializeObject<List<uinfor>>(json);
            con.Close();
            return list;
        }

        /// <summary>
        /// 用户专家关系表添加
        /// </summary>
        /// <returns></returns>
        public int Addurealation(uinfor add)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("insert into urealation values('" + add.uname + "','" + add.zname + "','" + add.uquestion + "','" + add.zanswer + "')", con);
            int i = cmd.ExecuteNonQuery();
            con.Close();
            return i;
        }
        /// <summary>
        /// 查询科室专家
        /// </summary>
        /// <param name="zid"></param>
        /// <returns></returns>
        public List<uinfor> chaxunkeshi(int zid)
        {
            string str = "";
            if (zid == 0)
            {
                str = "select a.kid, a= a.kname, b=c.kname,zcdate,cdate,znum,zcontent,flag from office a inner join expert b on a.zid=b.zid inner join office c on a.zid=c.kid";
            }
            else
            {
                str = "select a.kid, a= a.kname, b=c.kname,zcdate,cdate,znum,zcontent,flag from office a inner join expert b on a.zid=b.zid inner join office c on a.zid=c.kid   where a.zid=" + zid;
            }
            SqlDataAdapter sd = new SqlDataAdapter(str, con);
            DataTable st = new DataTable();
            sd.Fill(st);
            string str1 = JsonConvert.SerializeObject(st);
            List<uinfor> list = JsonConvert.DeserializeObject<List<uinfor>>(str1);
            return list;
        }
        /// <summary>
        /// 二级联动
        /// </summary>
        /// <param name="zid"></param>
        /// <returns></returns>
        public List<uinfor> typelist(int zid)
        {
            SqlCommand com = new SqlCommand("typelist", con);
            com.CommandType = CommandType.StoredProcedure;
            SqlParameter[] p ={
                                 new SqlParameter("@id",SqlDbType.Int),
                             };
            p[0].Value = zid;
            com.Parameters.AddRange(p);
            SqlDataAdapter sd = new SqlDataAdapter(com);
            DataTable st = new DataTable();
            sd.Fill(st);
            string str1 = JsonConvert.SerializeObject(st);
            List<uinfor> list = JsonConvert.DeserializeObject<List<uinfor>>(str1);
            return list;
        }
    }
}
